package com.sunzhenxing.action;

import com.mp.util.FindSamePerson;
import com.opensymphony.xwork2.ActionSupport;
import com.sunzhenxing.utility.Data;
import com.sunzhenxing.utility.Year;
import com.xuyankang.drawing.KPYear;

import java.io.Reader;
import java.sql.*;

import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.interceptor.ServletResponseAware;

//首先获取人员的ID，由于一个人可能有多个职位，也就是多条记录，可以根据工号+职位号获取人员的ID，也就是序号而不是工号
public class inputPersonReport extends ActionSupport implements ServletResponseAware{
	private static final long serialVersionUID = -2126517427961672423L;
	private HttpServletResponse response;
	private String pid;//人员工号
	private int person_id;//人员的ID编号，这个是不重复的，可以根据这个ID和年份来获取述职报告
	private int dutyid;//职位ID
	private String year;//获取年份
	private String reportContent;//报告的内容，插入或者更新的时候使用
	public void setPerson_id(int person_id) {
		this.person_id = person_id;
	}

	public int getPerson_id() {
		return person_id;
	}

	public String getReportContent() {
		return reportContent;
	}

	public void setReportContent(String reportContent) {
		this.reportContent = reportContent;
	}

	public String getYear() {
		return year;
	}

	public void setYear(String year) {
		this.year = year;
	}

	public String getPid() {
		return pid;
	}

	public void setPid(String pid) {
		this.pid = pid;
	}

	public int getDutyid() {
		return dutyid;
	}

	public void setDutyid(int dutyid) {
		this.dutyid = dutyid;
	}

	public void setServletResponse(HttpServletResponse response) {
		this.response=response;
	}
	
	public String view(){//获取指定被考评人的指定职位在指定年份的述职报告
		int pid=getID();
		int preId = FindSamePerson.findSamePersonId(year, Year.getYear(), pid);
		if (preId == -1) {
			preId = pid;
		}
		if (year.equals("2016")) {
			preId = pid;
		}
		String content="您在"+year+"年还没有提交该职位的述职报告！";
		String sql="select report_content from tb_report where owner_id="+preId+" and report_year='"+year+"' and owner_type='person'";
		Connection conn=Data.getConnection();
		PreparedStatement state=Data.getStatement(conn, sql);
		ResultSet result=Data.getResultSet(state);
		try{
			if(result.next()){
				content="";
				Clob clob=result.getClob(1);
				Reader contentReader=clob.getCharacterStream();
				char[] buffer=new char[1000];
				int length=0;
				while((length=contentReader.read(buffer))!=-1){
					content=content+new String(buffer,0,length);
				}
				contentReader.close();
			}
			conn.close();
			response.setCharacterEncoding("utf-8");
			response.getWriter().write(content);
			response.getWriter().flush();
			response.getWriter().close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return "error";
	}
	public String viewById(){
		String sql="select report_content from tb_report where owner_id="+person_id+" and owner_type!='dj_depart' and report_year='"+year+"'";
		Connection conn=Data.getConnection();
		String content="";
		PreparedStatement state=Data.getStatement(conn, sql);
		ResultSet result=Data.getResultSet(state);
		try{
			if(result.next()){
				content="";
				Clob clob=result.getClob(1);
				Reader contentReader=clob.getCharacterStream();
				char[] buffer=new char[1000];
				int length=0;
				while((length=contentReader.read(buffer))!=-1){
					content=content+new String(buffer,0,length);
				}
				contentReader.close();
			}
			conn.close();
			response.setCharacterEncoding("utf-8");
			response.getWriter().write(content);
			response.getWriter().flush();
			response.getWriter().close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return "error";
	}
	public String viewDJById(){
		String sql="select report_content from tb_report where owner_id="+person_id+" and owner_type='dj_depart' and report_year='"+year+"'";
		Connection conn=Data.getConnection();
		String content="";
		PreparedStatement state=Data.getStatement(conn, sql);
		ResultSet result=Data.getResultSet(state);
		try{
			if(result.next()){
				content="";
				Clob clob=result.getClob(1);
				Reader contentReader=clob.getCharacterStream();
				char[] buffer=new char[1000];
				int length=0;
				while((length=contentReader.read(buffer))!=-1){
					content=content+new String(buffer,0,length);
				}
				contentReader.close();
			}
			conn.close();
			response.setCharacterEncoding("utf-8");
			response.getWriter().write(content);
			response.getWriter().flush();
			response.getWriter().close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return "error";
	}
	public String action(){//根据用户的要求来更新或者插入述职报告
		response.setCharacterEncoding("utf-8");
		try {
			if(getActionSql()){
				//将tb_beassessed中的hasreport字段设置为1，代表已经提交述职报告
				int id=getID();
				Connection newConn=Data.getConnection();
				Statement newState=Data.getStatement(newConn);
				String newSql="update tb_beassessed set beassessed_hasreport=1 where beassessed_entity_id="+id;
				newState.executeUpdate(newSql);//将hasreport字段设置为1
				newState.close();
				newConn.close();
				response.getWriter().write("恭喜您，操作成功！");
				response.getWriter().flush();
				response.getWriter().close();
			}else{
				response.getWriter().write("数据库发生异常！");
				response.getWriter().flush();
				response.getWriter().close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "error";
	}
	//获取人员ID的方法
	private int getID(){
		int id=0;
		String sql="select id from tb_person where person_id='"+pid+"' and person_depart_id="+dutyid;//把职务ID全部替换成单位ID
		Connection conn=Data.getConnection();
		PreparedStatement state=Data.getStatement(conn, sql);
		ResultSet result=Data.getResultSet(state);
		try{
			if(result.next()){
				id=result.getInt(1);
			}
			result.close();
			state.close();
			conn.close();
			//System.out.println("getID中关闭连接！");
		}catch(Exception e){
			e.printStackTrace();
		}
		return id;//如果id=0则标准数据库中没有这条记录
	}
	//获取应该执行那个操作，update还是insert
	private boolean getActionSql(){
		int id=getID();
		boolean flag=false;
		try {
			String sql="select Report_ID from " +
			"tb_Report Where Owner_ID="+id+" and Owner_Type='person'"+" and Report_Year='"+year+"'";
			Connection conn=Data.getConnection();
			PreparedStatement state=Data.getStatement(conn, sql);
			ResultSet result=Data.getResultSet(state);
			if(result.next()){
				//执行更新操作
				int rid=result.getInt("Report_ID");
				try{
					Connection newconn=Data.getConnection();
					newconn.setAutoCommit(flag);
					String updateSql="update tb_Report set Report_Content=empty_clob() where Report_ID="+rid;
					PreparedStatement updateState=newconn.prepareStatement(updateSql);
					updateState.executeUpdate();
					String selectSql="select Report_Content from tb_Report where Report_ID="+rid+" for update";
					PreparedStatement selectState=newconn.prepareStatement(selectSql);
					ResultSet newresult=selectState.executeQuery();
					if(newresult.next()){
						Clob clob=newresult.getClob(1);
						clob.setString(1,reportContent);
					}
					newconn.commit();
					newconn.setAutoCommit(true);
					selectState.close();
					updateState.close();
					newconn.close();
					//System.out.println("连接关闭了！");
					flag=true;
				}catch(Exception e){
					e.printStackTrace();
				}
			}else{
				//执行插入操作
				try{
					Connection newconn=Data.getConnection();
					newconn.setAutoCommit(false);
					String insertSql="insert into tb_Report values("+"reportid.nextval,"+year+",empty_clob(),"+id+",'person')";
					PreparedStatement insertState=newconn.prepareStatement(insertSql);
					insertState.executeUpdate();
					String selectSql="select Report_Content from tb_Report where Owner_ID="+id+" and Report_Year="+year+" for update";
					PreparedStatement selectState=newconn.prepareStatement(selectSql);
					ResultSet newresult=selectState.executeQuery();
					if(newresult.next()){
						Clob clob=newresult.getClob(1);
						clob.setString(1,reportContent);
					}
					newconn.commit();
					newconn.setAutoCommit(true);
					selectState.close();
					insertState.close();
					newconn.close();
					//System.out.println("连接关闭了！");
					flag=true;
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			result.close();
			state.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public static String getReportContent(String pid, String type){
//		System.out.println("pid"+pid);
		String year = KPYear.getYear();
		String sql="select * from tb_Report where Owner_ID="+pid+" and Owner_Type='"+type+"' and Report_Year='"+year+"'";
		System.out.println(sql);
		Connection conn=Data.getConnection();
		PreparedStatement state=Data.getStatement(conn, sql);
		ResultSet result=Data.getResultSet(state);
		String content="";
		try {
			if(result.next()){
				Clob clob=result.getClob(3);
				Reader contentReader=clob.getCharacterStream();
				char[] buffer=new char[1000];
				int length=0;
				while((length=contentReader.read(buffer))!=-1){
					content=content+new String(buffer,0,length);
				}
				contentReader.close();
			}else{
				content="";
			}
			result.close();
			state.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return content;
	}
	
	public static String getReportContentByYear(String pid, String type, String year){
//		System.out.println("pid"+pid);
		String sql="select * from tb_Report where Owner_ID="+pid+" and Owner_Type='"+type+"' and Report_Year='"+year+"'";
		Connection conn=Data.getConnection();
		PreparedStatement state=Data.getStatement(conn, sql);
		ResultSet result=Data.getResultSet(state);
		String content="";
		try {
			if(result.next()){
				Clob clob=result.getClob(3);
				Reader contentReader=clob.getCharacterStream();
				char[] buffer=new char[1000];
				int length=0;
				while((length=contentReader.read(buffer))!=-1){
					content=content+new String(buffer,0,length);
				}
				contentReader.close();
			}else{
				content="";
			}
			result.close();
			state.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return content;
	}
}
